import pymysql


class StudentController:

    def __init__(self):
        dict01 = {
            "host": "localhost",
            "port": 3306,
            "user": "root",
            "password": "123456",
            "database": "stu2107",
            "charset": "utf8"
        }
        self.db = pymysql.connect(**dict01)
        self.cur = self.db.cursor()

    def __set_student_id(self, stu):
        sql = "select max(id) from student;"
        self.cur.execute(sql)
        # 一个查询结果
        sid = self.cur.fetchone()
        if sid:
            self.__start_id = sid[0]
            self.__start_id += 1
        else:
            self.__start_id = 1001
        stu.id = self.__start_id

    def add_student(self, stu):
        """
        1 录入学生信息
        """
        stdentList = []
        self.__set_student_id(stu)
        stdent = (stu.id, stu.name, stu.english, stu.python, stu.c)  # 将输入的学生信息保存到元组中
        stdentList.append(stdent)
        return stdentList

    def save(self, student):
        """
        将学生信息保存到数据库
        """
        try:
            arg = "insert into student value (%s,%s,%s,%s,%s);"
            self.cur.executemany(arg, student)
            self.db.commit()
            return True
        except Exception as e:
            self.db.rollback()
            raise e

    def find_student(self, value):
        id = ""
        name = ""
        if type(value) == int:
            id = value
        else:
            name = value
        if id:
            arg = "select * from student where id = %s;"
            self.cur.execute(arg, value)
        if name:
            arg = "select * from student where name = %s;"
            self.cur.execute(arg, value)
        stu = self.cur.fetchone()
        if stu:
            return stu
        return False

    def have_student(self):
        arg = "select count(*) from student;"
        self.cur.execute(arg)
        stu = self.cur.fetchone()
        if stu == 0:
            return False
        return stu[0]

    def remove(self, studentId):
        if self.have_student():
            arg = "select * from student where id = %s;"
            self.cur.execute(arg, studentId)
            stu = self.cur.fetchone()
            if stu:
                arg = "delete from student where id = %s;"
                self.cur.execute(arg, studentId)
                self.db.commit()
                return "ok"
            return "no"
        else:
            return False

    def amend(self, studentid):
        if self.have_student():
            arg = "select * from student where id = %s;"
            self.cur.execute(arg, studentid)
            stu = self.cur.fetchone()
            if stu:
                return True
        else:
            return False

    def update(self, key, new_info, sid):
        object = ("name", "english", "python", "c")
        # arg1 = "update student set name = %s where id = %s;"
        # arg2 = "update student set english = %s where id = %s;"
        # arg3 = "update student set python = %s where id = %s;"
        # arg4 = "update student set c = %s where id = %s;"
        # arg = eval(f"arg{key}")
        try:
            arg = "update student set " + object[key - 1] + " = %s where id = %s;"
            self.cur.execute(arg, [new_info, sid])
            self.db.commit()
            return True
        except Exception as e:
            self.db.rollback()
            raise e

    def orderby(self, mode, ascORdesc):
        judge = " ASC" if ascORdesc == "0" else " DESC"
        object = ("english", "python", "c", "english+python+c")
        arg = "select * from student order by " + object[mode - 1] + judge + ";"
        self.cur.execute(arg)
        all = self.cur.fetchall()
        return all

    def all_student(self):
        arg = "select * from student;"
        self.cur.execute(arg)
        stu = self.cur.fetchall()
        return stu

